﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmProductionStatement

    Sub FillInsurCompanies()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurCompanies As ArrayList = GetInsurCompaniesList()

            Me.CombInsurComp.Items.Clear()

            For Each InsurComp As String In InsurCompanies
                Me.CombInsurComp.Items.Add(InsurComp)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillInsurClasses()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurClasses As ArrayList = GetInsurClassList()

            Me.CombInsurClasses.Items.Clear()

            For Each InsurComp As String In InsurClasses
                Me.CombInsurClasses.Items.Add(InsurComp)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.ErrProv.Clear()
        Try
            If Me.RBranch.Checked = True And Me.CombBranch.SelectedIndex = -1 Then
                Me.ErrProv.SetError(Me.CombBranch, "Please select a valid branch from the list")

            ElseIf Me.RFilterInsurComp.Checked = True And Me.CombInsurComp.SelectedIndex = -1 Then
                Me.ErrProv.SetError(Me.CombInsurComp, "Please select a valid company from the list")

            ElseIf Me.RFilterInsurClass.Checked = True And Me.CombInsurClasses.SelectedIndex = -1 Then
                Me.ErrProv.SetError(Me.CombInsurClasses, "Please select a valid class from the list")
            Else
                Me.Cursor = Cursors.WaitCursor

                Dim CondBranch, CondFilter, ColDate, ColName As String

                If Me.RBranch.Checked = True Then
                    CondBranch = " and Branch=N'" & Me.CombBranch.Text & "' "
                Else
                    CondBranch = ""
                End If

                If Me.RFilterInsurComp.Checked = True Then
                    CondFilter = " and InsurComp=@InsurComp"

                ElseIf Me.RFilterInsurClass.Checked = True Then
                    CondFilter = " and ClassName=@ClassName"

                Else
                    CondFilter = ""
                End If

                If Me.RIssueDate.Checked = True Then
                    ColDate = "IssueDate"
                    ColName = "Issue Date"

                ElseIf Me.RInceptionDate.Checked = True Then
                    ColDate = "PeriodFrom"
                    ColName = "Inception Date"

                ElseIf Me.RTransDate.Checked = True Then
                    ColDate = "TransDate"
                    ColName = "Trans. Date (Entry Date)"
                End If

                Dim dap As New SqlDataAdapter("Select * From Policies Where DoneFin=1" & _
                                              "and " & ColDate & ">=N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                              "and " & ColDate & "<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:23:59' " & _
                                              CondBranch & CondFilter & " Order By " & ColDate, cnn)
                Dim das As New DataSet


                If Me.RFilterInsurComp.Checked = True Then
                    dap.SelectCommand.Parameters.AddWithValue("@InsurComp", Me.CombInsurComp.Text)

                ElseIf Me.RFilterInsurClass.Checked = True Then
                    dap.SelectCommand.Parameters.AddWithValue("@ClassName", Me.CombInsurClasses.Text)
                End If

                cnn.Open()
                dap.Fill(das, "Policies")
                cnn.Close()

                Dim rpt As New ProductionStatement
                rpt.SetDataSource(das)

                'Create Parameters

                Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
                Dim crParameterFieldDefinitions As ParameterFieldDefinitions
                Dim crParameterFieldLocation As ParameterFieldDefinition
                Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
                crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields


                'ColDate PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("ColDate")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = ColName
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                'MinDate PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


                'MaxDate PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                'Branch PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                If Me.RBranch.Checked = True Then
                    crParameterDiscreteValue.Value = Me.CombBranch.Text
                Else
                    crParameterDiscreteValue.Value = "ALL"
                End If
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                Me.CrystalReportViewer1.ReportSource = rpt

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RBranch_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RBranch.CheckedChanged
        If Me.RBranch.Checked = True Then
            Me.CombBranch.Enabled = True

        ElseIf Me.RAll.Checked = True Then
            Me.CombBranch.Enabled = False
        End If
    End Sub

    Sub CheckFilter()
        If Me.RFilterInsurComp.Checked = True Then
            Me.CombInsurComp.Enabled = True
            Me.CombInsurClasses.SelectedIndex = -1
            Me.CombInsurClasses.Enabled = False

        ElseIf Me.RFilterInsurClass.Checked = True Then
            Me.CombInsurComp.SelectedIndex = -1
            Me.CombInsurComp.Enabled = False
            Me.CombInsurClasses.Enabled = True

        ElseIf Me.RFilterAll.Checked = True Then
            Me.CombInsurComp.SelectedIndex = -1
            Me.CombInsurComp.Enabled = False
            Me.CombInsurClasses.SelectedIndex = -1
            Me.CombInsurClasses.Enabled = False
        End If
    End Sub

    Private Sub RFilterInsurComp_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RFilterInsurComp.CheckedChanged
        CheckFilter()
    End Sub

    Private Sub RFilterInsurClass_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RFilterInsurClass.CheckedChanged
        CheckFilter()
    End Sub

    Private Sub RFilterAll_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RFilterAll.CheckedChanged
        CheckFilter()
    End Sub

    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.CombBranch.Items.Clear()
            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombBranch.Items.Add(Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmPolicyReportsProductionStatement_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillInsurClasses()
        FillInsurCompanies()
        FillBranches()
    End Sub
End Class